package com.etc.dao.impl;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.etc.dao.SinggerDao;
import com.etc.entity.MusicStyle;
import com.etc.entity.Region;
import com.etc.entity.Singger;
import com.etc.util.DBUtil;

public class SinggerDaoImpl implements SinggerDao {
	
	@Override
	public List<Singger> findByUserId(Integer userId) {
		List<Singger> singgerList = new ArrayList<Singger>(); 
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT A.singger_id,")
		.append(" A.singger_name,")
		.append(" A.singger_img_path")
		.append(" from singger A inner join singger_collection B")
		.append(" on A.singger_id = B.singger_id")
		.append(" where B.user_id = ?");
		ResultSet rs = DBUtil.query(sb.toString(), userId);
		try {
			while (rs.next()) {
				   Integer singgerId = rs.getInt("singger_id"); //歌手编号
				   String singgerName = rs.getString("singger_name"); //歌手名
				   String singgerImgPath = rs.getString("singger_img_path");//歌手头像
				   
				   Singger singger = new Singger();
				   singger.setSinggerId(singgerId);
				   singger.setSinggerName(singgerName);
				   singger.setSinggerImgPath(singgerImgPath);
				   	
				   singgerList.add(singger);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return singgerList;
	}

	@Override
	public int countByUserId(Integer userId) {
		int count = 0;
		StringBuilder sb = new StringBuilder();
		sb.append("select")
		.append(" count(*)")
		.append(" from singger A inner join singger_collection B")
		.append(" on A.singger_id = B.singger_id")
		.append(" where user_id = ?");
		
		ResultSet rs = DBUtil.query(sb.toString(),userId);
		try {
			while (rs.next()) {
				count =  rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}


	@Override
	public List<Singger> findByCondition(String initials, String regionName, String gender, String styleName,int pageNum,int pageSize) {
        List<Singger> listSingger = new ArrayList<Singger>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select A.singger_id,")
		.append(" A.singger_name,")
		.append(" A.singger_img_path")
		.append(" from singger A")
		.append(" inner join region B on A.region_id=B.region_id")
		.append(" inner join music_style C on A.style_id=C.style_id");
		
		// 动态SQL
				sb.append(" where 1 = 1");
				
				// 判断参数情况
				if (initials != null && !"".equals(initials)) {
					sb.append(" and initials ='" + initials + "'");
				}
				if (regionName != null && !"".equals(regionName)) {
					sb.append(" and region_name ='" + regionName + "'");
				}
				if (gender!= null && !"".equals(gender)) {
					Integer n=null;
					if("男".equals(gender)) {
						n=0;
					}else if("女".equals(gender)) {
						n=1;
					}else if("组合".equals(gender)) {
						n=3;
					}
					sb.append(" and  gender ='" + n + "'");
				}
				if (styleName!= null && !"".equals(styleName)) {
					sb.append(" and style_name ='" + styleName + "'");
				}
				// 进行分页  select * from 表  where... limit a,b;
				sb.append(" limit " +  (pageNum - 1) * pageSize + "," + pageSize);

		ResultSet rs = DBUtil.query(sb.toString());
		try {
			while (rs.next()) {
				Integer singgerId  =  rs.getInt("singger_id");
				 String singgerName = rs.getString("singger_name");
				 String singgerImgPath = rs.getString("singger_img_path");
			
				// 歌手对象
				Singger singger = new Singger();
				singger.setSinggerId(singgerId);
				singger.setSinggerName(singgerName);
				singger.setSinggerImgPath(singgerImgPath);
				// 集合
				listSingger.add(singger);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return listSingger;
	}

	@Override
	public int countByCondition(String initials, String regionName, String gender, String styleName) {
     
		  int count=0;
		StringBuilder sb = new StringBuilder();
		sb.append("select ")
		.append(" count(*)")
		.append(" from singger A")
		.append(" inner join region B on A.region_id=B.region_id")
		.append(" inner join music_style C on A.style_id=C.style_id");
		
		// 动态SQL
				sb.append(" where 1 = 1");
				
				// 判断参数情况
				if (initials != null && !"".equals(initials)) {
					sb.append(" and initials ='" + initials + "'");
				}
				if (regionName != null && !"".equals(regionName)) {
					sb.append(" and region_name ='" + regionName + "'");
				}
				if (gender!= null && !"".equals(gender)) {
					Integer n=null;
					if("男".equals(gender)) {
						n=0;
					}else if("女".equals(gender)) {
						n=1;
					}else if("组合".equals(gender)) {
						n=2;
					}
					sb.append(" and  gender ='" + n + "'");
				}
				if (styleName!= null && !"".equals(styleName)) {
					sb.append(" and style_name ='" + styleName + "'");
				}
				
				

		ResultSet rs = DBUtil.query(sb.toString());
		try {
			while (rs.next()) {
				count =  rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// TODO Auto-generated method stub
		return count;
	}
	// ============================================ admin 操作 Dao ======================================
	
	@Override
	public List<Singger> findAll() {
		List<Singger> listSingger = new ArrayList<Singger>();
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT singger_id,")
		.append("singger_name,")
		.append("singger_img_path,")
		.append("B.style_name,")
		.append("gender,")
		.append("birthday,")
		.append("C.region_name,")
		.append("occupation,")
		.append("nationality,")
		.append("birthplace")
		.append(" FROM singger A INNER JOIN music_style B ON A.style_id = B.style_id")
		.append(" INNER JOIN region C ON C.region_id = A.region_id");
		ResultSet rs = DBUtil.query(sb.toString());
		try {
			while (rs.next()) {
				   Integer singgerId = rs.getInt("singger_id"); //歌手编号
				   String singgerName = rs.getString("singger_name"); //歌手名
				   String singgerImgPath = rs.getString("singger_img_path");//歌手头像
				   String styleName = rs.getString("style_name"); //类型名称
				   Integer gender = rs.getInt("gender"); //性别0男1女3组合
				   String birthday = rs.getString("birthday");//生日
				   String regionName = rs.getString("region_name"); //地区名
				   String occupation = rs.getString("occupation"); // 职业
				   String nationality = rs.getString("nationality"); // 国籍
				   String birthplace = rs.getString("birthplace");// 出生地
				   
				   Singger singger = new Singger();
				   singger.setSinggerId(singgerId);
				   singger.setSinggerName(singgerName);
				   singger.setSinggerImgPath(singgerImgPath);
				   singger.setGender(gender);
				   singger.setBirthday(birthday);
				   singger.setOccupation(occupation);
				   singger.setNationality(nationality);
				   singger.setBirthplace(birthplace);
				   MusicStyle musicStyle = new MusicStyle();
				   musicStyle.setStyleName(styleName);
				   singger.setMusicStyle(musicStyle );
				   Region region = new Region();
				   region.setRegionName(regionName);
				   singger.setRegion(region);
				   	
				   listSingger.add(singger);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return listSingger;
		
	}
	
	@Override
	public int addSinger(Singger singger) {
		int n = DBUtil.update("INSERT INTO singger(singger_id,singger_name,singger_img_path,style_id,gender,birthday,region_id,occupation,nationality,birthplace) VALUES(?,?,?,?,?,?,?,?,?,?)",
				singger.getSinggerId(),singger.getSinggerName(),singger.getSinggerImgPath(),
				singger.getMusicStyle().getStyleId(),singger.getGender(),singger.getBirthday(),singger.getRegion().getRegionId(),
				singger.getOccupation(),singger.getNationality(),singger.getBirthplace());
		return n;
	}

	@Override
	public int deleteSingerById(Integer singgerId) {
		return DBUtil.update("delete from singger where singger_id = ?", singgerId);
	}

	@Override
	public Singger findSingerInfoById(Integer singgerId) {
		Singger singger = null;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT singger_id,")
		.append("singger_name,")
		.append("singger_img_path,")
		.append("B.style_name,")
		.append("B.style_id,")
		.append("gender,")
		.append("birthday,")
		.append("C.region_name,")
		.append("C.region_id,")
		.append("occupation,")
		.append("nationality,")
		.append("birthplace")
		.append(" FROM singger A INNER JOIN music_style B ON A.style_id = B.style_id")
		.append(" INNER JOIN region C ON C.region_id = A.region_id")
		.append(" where singger_id = ? ");
		ResultSet rs = DBUtil.query(sql.toString(),singgerId);
		try {
			while (rs.next()) {
				   singgerId = rs.getInt("singger_id"); //歌手编号
				   String singgerName = rs.getString("singger_name"); //歌手名
				   String singgerImgPath = rs.getString("singger_img_path");//歌手头像
				   String styleName = rs.getString("style_name"); //类型名称
				   Integer styleId = rs.getInt("style_id");
				   Integer gender = rs.getInt("gender"); //性别0男1女3组合
				   String birthday = rs.getString("birthday");//生日
				   String regionName = rs.getString("region_name"); //地区名
				   Integer regionId = rs.getInt("region_id");
				   String occupation = rs.getString("occupation"); // 职业
				   String nationality = rs.getString("nationality"); // 国籍
				   String birthplace = rs.getString("birthplace");// 出生地
				   
				   singger = new Singger();
				   singger.setSinggerId(singgerId);
				   singger.setSinggerName(singgerName);
				   singger.setSinggerImgPath(singgerImgPath);
				   singger.setGender(gender);
				   singger.setBirthday(birthday);
				   singger.setOccupation(occupation);
				   singger.setNationality(nationality);
				   singger.setBirthplace(birthplace);
				   MusicStyle musicStyle = new MusicStyle();
				   musicStyle.setStyleName(styleName);
				   musicStyle.setStyleId(styleId);
				   singger.setMusicStyle(musicStyle );
				   Region region = new Region();
				   region.setRegionName(regionName);
				   region.setRegionId(regionId);
				   singger.setRegion(region);
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return singger;
	}

	@Override
	public List<Singger> findSingerInfoBySingerName(String singgerName, int pageNum,int pageSize) {
		List<Singger> listSingger = new ArrayList<Singger>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT singger_id,")
		.append("singger_name,")
		.append("singger_img_path,")
		.append("B.style_name,")
		.append("gender,")
		.append("birthday,")
		.append("C.region_name,")
		.append("occupation,")
		.append("nationality,")
		.append("birthplace")
		.append(" FROM singger A INNER JOIN music_style B ON A.style_id = B.style_id")
		.append(" INNER JOIN region C ON C.region_id = A.region_id");		
		sb.append(" where 1 = 1");
		if (singgerName != null && !"".equals(singgerName)) {
			sb.append(" and singger_name like  '%" + singgerName + "%'");
		}
		sb.append(" limit " +  (pageNum - 1) * pageSize + "," + pageSize);
		ResultSet rs = DBUtil.query(sb.toString());
		try {
			while (rs.next()) {
				   Integer singgerId = rs.getInt("singger_id"); //歌手编号
				   singgerName = rs.getString("singger_name"); //歌手名
				   String singgerImgPath = rs.getString("singger_img_path");//歌手头像
				   String styleName = rs.getString("style_name"); //类型名称
				   Integer gender = rs.getInt("gender"); //性别0男1女3组合
				   String birthday = rs.getString("birthday");//生日
				   String regionName = rs.getString("region_name"); //地区名
				   String occupation = rs.getString("occupation"); // 职业
				   String nationality = rs.getString("nationality"); // 国籍
				   String birthplace = rs.getString("birthplace");// 出生地
				   
				   Singger singger = new Singger();
				   singger.setSinggerId(singgerId);
				   singger.setSinggerName(singgerName);
				   singger.setSinggerImgPath(singgerImgPath);
				   singger.setGender(gender);
				   singger.setBirthday(birthday);
				   singger.setOccupation(occupation);
				   singger.setNationality(nationality);
				   singger.setBirthplace(birthplace);
				   MusicStyle musicStyle = new MusicStyle();
				   musicStyle.setStyleName(styleName);
				   singger.setMusicStyle(musicStyle );
				   Region region = new Region();
				   region.setRegionName(regionName);
				   singger.setRegion(region);
				   	
				   listSingger.add(singger);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return listSingger;
	}

	@Override
	public int countBySingerName(String singgerName) {
		int count = 0;
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT")
		.append(" count(*)")
		.append(" FROM singger");
		
		sb.append(" where 1 = 1");
		if (singgerName != null && !"".equals(singgerName)) {
			sb.append(" and singger_name like  '%" + singgerName + "%'");
		}
		ResultSet rs = DBUtil.query(sb.toString());
		try {
			while (rs.next()) {
				count =  rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public int modifySingerInfo(Singger singger) {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE singger SET ")
		.append("singger_id =?,")
		.append("singger_name =?,")
		.append("style_id =?,")
		.append("gender =?,")
		.append("birthday =?,")
		.append("region_id =?,")
		.append("occupation =?,")
		.append("nationality =?,")
		.append("birthplace =?");
		if (singger.getSinggerImgPath() != null) {
			sql.append(",singger_img_path = '" +singger.getSinggerImgPath()+ "'");
		}
		sql.append(" where singger_id =?");
		
		return DBUtil.update(sql.toString(), singger.getSinggerId(),singger.getSinggerName(),
				singger.getMusicStyle().getStyleId(),singger.getGender(),singger.getBirthday(),singger.getRegion().getRegionId(),
				singger.getOccupation(),singger.getNationality(),singger.getBirthplace(),singger.getSinggerId());
	}

	@Override
	public Singger finSinggerBySingerName(String singgerName) {
		Singger singger = null;
		ResultSet rs = DBUtil.query("SELECT singger_id FROM singger WHERE singger_name= ? ", singgerName);
		try {
			while (rs.next()) {
				Integer singgerId = rs.getInt("singger_id");
				singger = new Singger();
				singger.setSinggerId(singgerId);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return singger;
	}

	


}
